/**
 * 2017年8月3日 星期四
 * 访问IP地址黑名单检测
 * >> param
    * @ip 地址
    * @ct 标准起始次数
    * @mi 标准起始时长
    * @mx 最大访问限度
 * >> return
 *  {code: 代码, msg: 消息, vid: vip_blacklist_id, num: 请求数据}
 *
 *  |   code => 2000: 请求有效; 1001: 锁定状态; 1000: 死锁态; -1 非法参数
 *  |   
 * 示例： 
 *      1.  call vip_blacklist_ck(null, null, null);
 */

drop procedure if exists `vip_blacklist_ck`; -- 同同时执行报错
delimiter //
create procedure `vip_blacklist_ck`(
    ip varchar(25),        
    ct int,
    mi decimal(4,1),
    mx int
)
this_sp:begin
    declare vcode int default -1;
    declare msg varchar(300) default '';
    declare vcount int default -1;
    declare rvcount int default 0;
    declare v_max_ctt int default 40;
    declare vdt date default curdate();            -- date_format(now(), '%Y-%m-%d');
    declare min_time datetime;

    -- vip_blacklist
    declare vipbl_id int;       -- 数据id
    declare vlock_mk char(1) default 'N';
    declare vlock_mtime int;    --  default UNIX_TIMESTAMP();
    declare vlock_dtime int;

    -- 最大可承受值
    if mx is null then
        set mx = v_max_ctt;
    end if;

    if ip is null or ip = '' then
        select 
            vcode as `code`, 
            '请求失败： ip地址必填，且标准起始次数、起始时长为空时分别默认为5、5分钟' as `msg`,
            vipbl_id as `vid`,
            vcount as `num`,
            mx as `max`
            ;
        leave this_sp;
    end if;
    -- 输入参数检测
    -- 起始次数
    if ct is null or ct < 1 then
        set ct = 5;
    end if;
    -- 起始时间
    if mi is null or mi < 1 then
        set mi = 5;
    end if;
    
    
    -- ip 黑名单表
    -- 默认时间值以及活动设置值
    -- select `listid`,`lock_dtime` into vipbl_id, vlock_dtime from `vst_blackip` where  `ip` = ip and `mtime` like concat('%',vdt, '%') limit 1;
    
    select 
            `listid`, `lock_mk`, `lock_mtime`, `lock_dtime` into vipbl_id, vlock_mk, vlock_mtime, vlock_dtime
        from `vst_blackip` where `date` = vdt and `ip` = ip order by `mtime` desc limit 1 ;

    if vlock_dtime is not null then
        set mi = vlock_dtime;
    end if;

    set min_time = date_add(now(), INTERVAL -mi MINUTE);

    -- 调试输出
    -- select min_time;leave this_sp;

    -- 时间范围内的访问值
    select count(*) into rvcount from `vst_blackip_logs` where `ip` = ip and `mtime` like concat('%',vdt, '%') and `mtime` >= min_time;
    -- 总次数
    select count(*) into vcount from `vst_blackip_logs` where `ip` = ip and `mtime` like concat('%',vdt, '%');
    
    
    -- 超过警戒值(报警值)
    if vcount >= mx then        
        select 1000 as `code`, 
            concat(ip,'于', vdt, '请求次数已经超过最大请求限定，该ip 服务将被限制') as `msg`,
            vipbl_id as `vid`,
            vcount as `num`,
            mx as `max`
            ;
        leave this_sp;
    -- 安全范围内
    elseif rvcount < ct then
        select 2000 as `code`, 
            concat(ip,'访问有效') as `msg`,
            vipbl_id as `vid`,
            vcount as `num`,
            mx as `max`
            ;
        leave this_sp;

    -- 请求被锁定        
    elseif rvcount >= ct then
        select 1001 as `code`, 
            concat(ip,'首次被锁定，由于访问达到了安全限') as `msg`,
            vipbl_id as `vid`,
            vcount as `num`,
            mx as `max`
            ;
        leave this_sp;
    end if;
  
    if vipbl_id is not null then
        if vlock_mk = 'Y' then
            -- 锁定有效范围内
            if vlock_dtime > (unix_timestamp() - vlock_mtime) then
                select 1001 as `code`, 
                    concat(ip,'处于锁定状态(', vdt, ')') as `msg`,
                    vipbl_id as `vid`
                    ;
                leave this_sp;
            else
                -- 锁定时长计算
                update `vst_blackip` set `lock_mk` = 'N' where 'listid' = vipbl_id;
            end if;
        end if;
    end if;
    
    select 2000 as `code`, 
        concat(ip,'处于安全状态(', vdt, ')') as `msg`,
        vipbl_id as `vid`,
        vcount as `num`,
        mx as `max`
        ;

end;